-- 统计视频观看数Top10

SELECT videoid,views FROM gulivideo_orc ORDER BY views DESC LIMIT 10;

-- 统计视频类别热度Top10

SELECT category,SUM(views) hot FROM gulivideo_orc GROUP BY category ORDER BY hot DESC LIMIT 10;

-- 统计视频观看数Top20所属类别

SELECT  views,category FROM    gulivideo_orc ORDER BY views DESC LIMIT 20;

-- 统计出视频观看数最高的20个视频的所属类别    以及类别包含Top20视频的个数 
-- 先计算观看书最高的20个视频，得到视频id

SELECT category_name,COUNT(videoid) hot_count
FROM (
    SELECT videoid, views,category_name
FROM (
    SELECT * FROM gulivideo_orc  
    ORDER BY views DESC LIMIT 20 ) t1 LATERAL VIEW explode(t1.category) temp as category_name 
) t2 GROUP BY category_name ORDER BY hot_count DESC;


-- 统计视频观看数Top50 所关联视频 的所属类别Rank
-- 要把 关联视频展开   

SELECT 
category_name ,
COUNT(videoId) hot
FROM (
    SELECT videoId,views,relatedid,category FROM
(
  SELECT videoId,views,relatedid,category FROM gulivideo_orc ORDER BY views LIMIT 50
) t0 LATERAL VIEW explode(t0.relatedid) related as related_id
) t1 LATERAL VIEW explode(t1.category) category as category_name
GROUP BY category_name ORDER BY hot DESC;


select 
    category_name as category, 
    count(t5.videoId) as hot 
from (
    select 
        videoId, 
        category_name 
    from (
        select 
            distinct(t2.videoId), 
            t3.category 
        from (
            select 
                explode(relatedId) as videoId 
            from (
                select 
                    * 
                from 
                    gulivideo_orc 
                order by 
                    views 
                desc limit 
                    1)  t1) t2 
        inner join 
            gulivideo_orc t3 on t2.videoId = t3.videoId) t4 lateral view explode(category) t_catetory as category_name) t5
group by 
    category_name 
order by 
    hot 
desc;




-- 统计每个类别中的视频热度Top10 一Music 为例
SELECT videoid,views FROM 
(SELECT * 
FROM gulivideo_orc LATERAL VIEW explode(category) temp as category_name WHERE category_name = 'Music' ) t1
ORDER BY views DESC LIMIT 10;



-- 统计每个类别中视频流量Top10 以Music为例
-- 创建临时表 其实就是把 gulivideo 按照category 展开

CREATE TABLE IF NOT EXISTS gulivideo_category
(

    videoId string, 
    uploader string, 
    age int, 
    categoryId string, 
    length int, 
    views int, 
    rate float, 
    ratings int, 
    comments int, 
    relatedId array<string>
) row format delimited
fields terminated by '\t'
collection items terminated by '&'
stored as orc;

INSERT OVERWRITE TABLE gulivideo_category
SELECT
        videoId,
        uploader,
        age,
        categoryId,
        length,
        views,
        rate,
        ratings,
        comments,
        relatedId 
FROM
    gulivideo_orc LATERAL VIEW explode(category) catetory as categoryId;



SELECT  videoId
       ,views
       ,ratings
FROM gulivideo_category
WHERE categoryId = "Music"
ORDER BY ratings desc 
LIMIT 10;

-- 统计上传视频最多的用户Top10以及他们上传的观看次数在前20的视频
-- 上传视频最多的用户
SELECT videoId,views FROM (
    SELECT * FROM 
 gulivideo_orc ORDER BY views LIMIT 20
) t0 JOIN (
SELECT uploader,COUNT(videoId) video_count 
FROM gulivideo_orc GROUP BY uploader ORDER BY  video_count DESC LIMIT 10
) t1 ON  t0.uploader = t1.uploader;

-- 统计每个类别视频观看数Top10
-- 每个类别  视频观看数   TOP10
SELECT t1.*
FROM
(
    SELECT videoId,categoryId,views,
        ROW_NUMBER() OVER(PARTITION BY categoryId ORDER BY views desc) rank
        FROM gulivideo_category 
) t1 WHERE t1.rank <= 10;





